# -*- coding: UTF-8 -*-
import datetime
import os

import openpyxl
import pandas as pd
from bs4 import BeautifulSoup


from datasource.interface_fundincome import InterfaceFundIncome
from datasource.interface_fundperformance import InterfaceFundPerformance
from datasource.html_fundoverview import HTMLFundOverview
from datasource.html_fundmanager import HTMLFundManager

'''
数据清洗，并得到5个表格（详见README.md）
rate of return - 收益率
income - 收益
'''
class DataClean(object):

    def __init__(self):
        self.td = datetime.date.today()
        self.xlsxname = '基金数据_' + str(datetime.date.today())

    def data2xlsx(self, sheetname, datas_df):

        path = os.path.dirname(os.path.abspath('.')) + '/data/' + self.xlsxname + '.xlsx'
        # writer = pd.ExcelWriter(path)
        # 存在返回的是TRUE, 不存在返回的是FALSE
        # if os.path.exists(path):
        #     wb = openpyxl.load_workbook(path)
        #     # 获取workbook中所有的表格
        #     sheets = wb.sheetnames
        #     if sheetname in sheets:
        #         wb.remove(wb[sheetname])  # 删除 对应的sheet页

        writer = pd.ExcelWriter(path)
        datas_df.to_excel(writer, sheet_name=sheetname)
        # 必须运行writer.save()，不然不能输出到本地
        writer.save()

    # 基金业绩原始数据
    def createtable_fundperformance(self):

        sd = datetime.date(self.td.year - 1, 1, 1)
        ed = datetime.date(self.td.year - 1, 12, 31)
        pn = 5000
        ifp = InterfaceFundPerformance(sd, ed, pn)
        return ifp.getdata_fundperformance()  # 混合基金的原始数据

    # 表1: | 基金代码 | 基金简称 | 基金类型 | 基金成立日期 | 近1年业绩 | 近2年业绩 | 近3年业绩 | 资金规模(亿) |
    def createtable_1(self):

        fp_df = self.createtable_fundperformance()
        assertsizes = []
        cols = ["基金代码", "基金简称", "基金成立日期", "近1年业绩", "近2年业绩", "近3年业绩"]
        date1 = datetime.date(self.td.year - 3, 1, 1)  # datetime.date(td.year - 1, td.month, td.day) - 去年的今天

        # print("len_fp_dataframe = %d " % fp_df.shape[0])
        len_fp_dataframe = int(fp_df.shape[0] / 4)

        df = fp_df.loc[:, cols]
        # print(df.columns)
        df["基金成立日期"] = pd.to_datetime(df["基金成立日期"])
        # 只保留成立时间大于3年的
        for index, row in df.iterrows():
            if row["基金成立日期"] > date1:
                df.drop(index=index, inplace=True)
        # print('len(df) = %d ' % df.shape[0])
        # 转换列数据的类型
        df["近1年业绩"] = df["近1年业绩"].astype("float")
        df["近2年业绩"] = df["近2年业绩"].astype("float")
        df["近3年业绩"] = df["近3年业绩"].astype("float")
        if df.shape[0] > len_fp_dataframe:
            df.sort_values(by="近1年业绩", inplace=True, ascending=False)
            df_1 = df[0:len_fp_dataframe]
            df.sort_values(by="近2年业绩", inplace=True, ascending=False)
            df_2 = df[0:len_fp_dataframe]
            df.sort_values(by="近3年业绩", inplace=True, ascending=False)
            df_3 = df[0:len_fp_dataframe]
            df = pd.merge(pd.merge(df_1, df_2, on=cols), df_3, on=cols)
        # print('len(df2) = %d ' % df.shape[0])
        # df.reset_index(drop=True, inplace=True)  # 排列后的索引是乱序的，重置索引，从0开始
        # df.index = df.index + 1
        # print('df.columns :')
        # print(df.columns)
        # print('df.index :')
        # print(df.index)
        # print(df.iloc[260, 1])
        # df.shape[0] 等同于 len(df)，返回行数；df.shape[1] 返回列数；df.shapes 返回二维数组的长宽

        for code in df["基金代码"]:
            hfo = HTMLFundOverview(code)
            assertsizes.append(hfo.get_assetsize())
        df['资金规模(亿)'] = assertsizes

        for index, row in df.iterrows():
            if row["资金规模(亿)"] > 100 or row["资金规模(亿)"] < 20:
                df.drop(index=index, inplace=True)


        # 筛选资金规模在[20,100]区间内的基金
        # df['资金规模(亿)'] = None
        # print(df.columns)
        # d_index = list(df.columns).index('资金规模(亿)')
        # print('d_index = %d' % d_index)
        # for index, row in df.iterrows():
        #     hfo = HTMLFundOverview(row["基金代码"])
        #     assertsize = hfo.get_assetsize()
        #     if assertsize <= 100 and assertsize >= 20:
        #         # print(assertsize)
        #         print(index)
        #         df.iloc[index, d_index] = assertsize
        #     else:
        #         df.drop(index=index, inplace=True)

        strdump = '混合型,' * df.shape[0]
        strdump = strdump.rstrip(',')
        strls = strdump.split(',')
        df["基金类型"] = strls
        df.reset_index(drop=True, inplace=True)  # 排列后的索引是乱序的，重置索引，从0开始
        df.index = df.index + 1
        # self.data2xlsx('混合型基金筛选后的数据', df)
        return df


        # datas_clean = datas_df_fp.loc[:, ["基金代码", "基金简称", "基金成立日期", "近1年业绩", "近2年业绩", "近3年业绩"]]
        # assertsizes = []
        # for code in datas_clean["基金代码"]:
        #     hfo = HTMLFundOverview(code)
        #     assertsizes.append(hfo.get_assetsize())
        # datas_clean['资金规模(亿)'] = assertsizes
        # return datas_clean



    # 基金收益原始数据
    def json2pandas_fundincome(self):

        dfi = InterfaceFundIncome('jQuery18307394209131639307_1582695425022', '1582695425036')
        json_data = dfi.get_response()
        # print('json_data["data"]: %s' % json_data['data'])
        soup = BeautifulSoup(str(json_data['data']), "lxml")
        # table = soup.table

        # 获取列名
        thead = soup.thead
        ths = thead.find_all('th')
        cols = []
        for th in ths:
            # print(th.get_text())
            if ths.index(th) == 2:
                cols.append(th.get_text())
            elif ths.index(th) == 3:
                cols.append(th.get_text())
            elif ths.index(th) == 5:
                cols.append(th.get_text())
            elif ths.index(th) == 7:
                cols.append(th.get_text())
            elif ths.index(th) == 8:
                cols.append(th.get_text())
            elif ths.index(th) == 9:
                cols.append(th.get_text())
            elif ths.index(th) == 11:
                cols.append(th.get_text())
            elif ths.index(th) == 12:
                cols.append(th.get_text())
        # 获取二维数据
        tbody = soup.tbody
        trs = tbody.find_all('tr')
        rows = []
        for tr in trs:
            tds = tr.find_all('td')
            row = []

            for td in tds:
                if tds.index(td) == 2:
                    row.append(td.get_text())
                elif tds.index(td) == 3:
                    row.append(td.get_text())
                elif tds.index(td) == 5:
                    row.append(float(td.get_text()))
                elif tds.index(td) == 7:
                    row.append(float(td.get_text().strip("%")))
                elif tds.index(td) == 8:
                    if td.get_text() != '--':
                        row.append(float(td.get_text().strip("%")))
                    else:
                        row.append(td.get_text())
                elif tds.index(td) == 9:
                    if td.get_text() != '--':
                        row.append(float(td.get_text().strip("%")))
                    else:
                        row.append(td.get_text())
                elif tds.index(td) == 11:
                    if td.get_text() == '暂无评级':
                        row.append(0)
                    else:
                        row.append(len(td.get_text()))
                elif tds.index(td) == 12:
                    row.append(float(td.get_text().strip("%")))

            if (row[4] != '--') & (row[5] != '--'):
                rows.append(row)
        return pd.DataFrame(rows, index=list(range(1, len(rows) + 1)), columns=cols)

        # pass

    # 基金经理的信息
    def json2pandas_fundmanage(self, ls_fundcode):

        for fundcode in ls_fundcode:
            fm = HTMLFundManager(fundcode)
            # fm.getfundmanagerinfo()
            print('\n')
            dc = fm.getmanagefundlist()


        # pass

    # 基金的信息：规模，成立日期，基金经理等
    def json2pandas_fundinfo(self, ls_fundcode):
        ls_fundinfo = []
        for fundcode in ls_fundcode:
            fbp = HTMLFundOverview(fundcode)
            # fbp.getfundbasicinfo()
            # dc_fbp = fbp.getfundbasictable()
            ls_fundinfo.append(fbp.getfundbasictable())

        # 用列表字典生成 DataFrame
        return pd.DataFrame(ls_fundinfo)

        # pass



if __name__ == '__main__':

    dc = DataClean()

    df = dc.createtable_fundperformance()
    # dc.data2xlsx("基金业绩原始数据", df)
    fp_df = dc.createtable_1()
    # dc.data2xlsx("混合型基金筛选后的数据", fp_df)

    path = os.path.dirname(os.path.abspath('.')) + '/data/' + dc.xlsxname + '.xlsx'
    writer = pd.ExcelWriter(path)
    df.to_excel(writer, sheet_name="基金业绩原始数据")
    fp_df.to_excel(writer, sheet_name="混合型基金筛选后的数据")
    writer.save()     # 必须运行writer.save()，不然不能输出到本地

    print('len(fp_df) = %d' % fp_df.shape[0])
